SET NOCOUNT ON

PRINT 'Resetting all procedures and triggers related to the film sub-schema'
IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_delete_award_orphans'
        AND type='TR')
DROP TRIGGER t_delete_award_orphans
GO

IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_delete_genre_orphans'
        AND type='TR')
DROP TRIGGER t_delete_genre_orphans
GO

IF EXISTS (SELECT name FROM dbo.sysobjects
        WHERE name='t_delete_person_orphans'
        AND type='TR')
DROP TRIGGER t_delete_person_orphans
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_edition_by_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_edition_by_film
GO
IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_crew_by_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_crew_by_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_awards_by_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_awards_by_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_genre_by_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_genre_by_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_by_isan' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_by_isan
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_by_title' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_by_title
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_by_person' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_by_person
GO


IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_by_genre' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_by_genre
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_by_award' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_by_award
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_display_film_between_dates' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_display_film_between_dates
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_new_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_new_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_person' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_person
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_genre' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_genre
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_award' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_award
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_film' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_film
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_new_persons' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_new_persons
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_new_genres' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_new_genres
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_insert_new_awards' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_insert_new_awards
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_link_played_in' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_link_played_in
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_link_film_genre' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_link_film_genre
GO

IF EXISTS (SELECT name  
           FROM  sysobjects 
           WHERE name = 'p_link_film_award' AND
                 type = 'P')
    DROP PROCEDURE dbo.p_link_film_award
GO


-------------------------------------------------------------------------------
-- Trigger for deleting orphans from award
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE TRIGGER t_delete_award_orphans ON dbo.Film_Award
FOR DELETE
AS
BEGIN
    DECLARE @film AS BIGINT
    DECLARE @name AS VARCHAR(64)
    DECLARE @year AS INTEGER

    DECLARE award_cursor CURSOR
    FOR
        SELECT id_film, award_name, award_year
          FROM DELETED;

    IF @@ERROR <> 0
        BEGIN
            PRINT '[T_DELETE_AWARD_ORPHANS] Error removing orphans from Award'
            RETURN
        END

    OPEN award_cursor


    PRINT '[T_DELETE_AWARD_ORPHANS] Removing orphans from Award'
    FETCH NEXT FROM award_cursor INTO @film, @name, @year
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DELETE From Award WHERE name = @name AND year = @year;
            FETCH NEXT FROM award_cursor INTO @film, @name, @year
        END

    CLOSE award_cursor;
    DEALLOCATE award_cursor;

END
GO

-------------------------------------------------------------------------------
-- Trigger for deleting orphans from genre
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE TRIGGER t_delete_genre_orphans ON dbo.Film_Genre
FOR DELETE
AS
BEGIN
    DECLARE @film AS BIGINT
    DECLARE @name AS VARCHAR(64)

    DECLARE genre_cursor CURSOR
    FOR
        SELECT id_film, genre_name
          FROM DELETED;

    IF @@ERROR <> 0
        BEGIN
            PRINT '[T_DELETE_GENRE_ORPHANS] Error removing orphans from Genre'
            RETURN
        END

    OPEN genre_cursor

    FETCH NEXT FROM genre_cursor INTO @film, @name;

    PRINT '[T_DELETE_GENRE_ORPHANS] Removing orphans from Genre'
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DELETE From Genre WHERE name = @name;
            FETCH NEXT FROM genre_cursor INTO @film, @name;
        END

    CLOSE genre_cursor;
    DEALLOCATE genre_cursor;
END
GO

-------------------------------------------------------------------------------
-- Trigger for deleting orphans from Person
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE TRIGGER t_delete_person_orphans ON dbo.Played_In
FOR DELETE
AS
BEGIN
    DECLARE @film AS BIGINT
    DECLARE @firstname AS VARCHAR(64)
    DECLARE @lastname AS VARCHAR(64)

    DECLARE person_cursor CURSOR
    FOR
        SELECT id_film, firstname, lastname
          FROM DELETED;

    IF @@ERROR <> 0
        BEGIN
            PRINT '[T_DELETE_PERSON_ORPHANS] Error removing orphans from Person'
            RETURN
        END

    OPEN person_cursor

    FETCH NEXT FROM person_cursor INTO @film, @firstname, @lastname

    PRINT '[T_DELETE_PERSON_ORPHANS] Removing orphans from Person'
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DELETE From Person WHERE firstname = @firstname 
                                 AND lastname = @lastname;
            FETCH NEXT FROM person_cursor INTO @film, @firstname, @lastname
        END

    CLOSE person_cursor;
    DEALLOCATE person_cursor;
END
GO



-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film
AS 
BEGIN
    PRINT ' * Person playing in film'
    SELECT p.firstname, p.lastname, p.birthdate, p_i.id_film AS Film
      FROM Person p, Played_in p_i 
     WHERE p.firstname = p_i.firstname 
       AND p.lastname = p_i.lastname;

    PRINT ' * Film''s genre'
    SELECT g.name, gf.id_film AS Film
      FROM Genre g, Film_Genre gf
     WHERE g.name = gf.genre_name;

    PRINT ' * Film''s awards'
    SELECT * 
      FROM Award a, Film_Award af
     WHERE a.name = af.award_name
       AND a.year = af.award_year;

    PRINT ' * Film listing'
    SELECT *
      FROM Film f;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display the crew of a given film 
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_crew_by_film
    @isan   NVARCHAR(64)
AS 
BEGIN
    IF @isan <= 0
        BEGIN
            PRINT '[P_DISPLAY_EDITION_BY_FILM] ISAN shall be positive.'
            RETURN -1
        END
    PRINT ' * Person playing in film'
    SELECT p_i.role, p.firstname, p.lastname, p.birthdate, p_i.id_film AS Film
      FROM Person p, Played_in p_i 
     WHERE p.firstname = p_i.firstname 
       AND p.lastname = p_i.lastname
       AND p_i.id_film = @isan;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display the awards of a given film 
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_awards_by_film
    @isan   NVARCHAR(64)
AS 
BEGIN
    IF @isan <= 0
        BEGIN
            PRINT '[P_DISPLAY_EDITION_BY_FILM] ISAN shall be positive.'
            RETURN -1
        END
    PRINT ' * Film''s awards'
    SELECT * 
      FROM Award a, Film_Award af
     WHERE a.name = af.award_name
       AND a.year = af.award_year
       AND af.id_film = @isan;

    RETURN 0
END
GO
-------------------------------------------------------------------------------
-- Procedure to display the genre list of a given film 
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_genre_by_film
    @isan   NVARCHAR(64)
AS 
BEGIN
    IF @isan <= 0
        BEGIN
            PRINT '[P_DISPLAY_EDITION_BY_FILM] ISAN shall be positive.'
            RETURN -1
        END
    PRINT ' * Film''s genre'
    SELECT g.name, gf.id_film AS Film
      FROM Genre g, Film_Genre gf
     WHERE g.name = gf.genre_name
       AND gf.id_film = @isan;

    RETURN 0
END
GO


-------------------------------------------------------------------------------
-- Procedure to display a given film by isan
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_by_isan
    @isan   NVARCHAR(64)
AS 
BEGIN
    IF @isan <= 0
        BEGIN
            PRINT '[P_DISPLAY_FILM_BY_ISAN] ISAN shall be positive.'
            RETURN -1
        END
    PRINT ' * Film listing'
    SELECT *
      FROM Film f
     WHERE isan = @isan;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a given film by title
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_by_title
    @title   NVARCHAR(64)
AS 
BEGIN
    IF @title = '' OR @title = NULL
        BEGIN
            PRINT '[P_DISPLAY_EDITION_BY_TITLE] ISAN shall be positive.'
            RETURN -1
        END
    PRINT ' * Film by title'
    SELECT *
      FROM Film f
     WHERE title = @title;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata by person
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_by_person
    @firstname   NVARCHAR(64),
    @lastname    NVARCHAR(64)
AS 
BEGIN
    IF @firstname = NULL OR @firstname = '' OR @lastname = NULL OR @lastname = ''
        BEGIN
            PRINT '[P_DISPLAY_FILM_BY_PERSON] Firstname and lastname shall be filled.'
            RETURN -1
        END
    PRINT ' * Film listing by person : '+@firstname+' '+@lastname
    SELECT *
      FROM Film f, Played_In pi
     WHERE f.isan = pi.id_film
       AND pi.firstname = @firstname
       AND pi.lastname = @lastname;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata by genre
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_by_genre
    @name   NVARCHAR(64)
AS 
BEGIN
    IF @name = NULL OR @name = ''
        BEGIN
            PRINT '[P_DISPLAY_FILM_BY_GENRE] Genre''s name shall be filled.'
            RETURN -1
        END


    PRINT ' * Film listing by genre : '+@name
    SELECT *
      FROM Film f, Film_Genre fg
     WHERE f.isan = fg.id_film
       AND fg.genre_name = @name;

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata by award
-- NULL for name or year display only the other
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_by_award
    @name   NVARCHAR(64),
    @year   INTEGER
AS 
BEGIN
    PRINT ' * Film listing by award '+@name+' '+@year
    if @name IS NULL AND NOT @year IS NULL
        SELECT *
          FROM Film f, Film_Award fa
         WHERE f.isan = fa.id_film
           AND fa.award_year = @year;
    if @year IS NULL AND NOT @name IS NULL
        SELECT *
          FROM Film f, Film_Award fa
         WHERE f.isan = fa.id_film
           AND fa.award_name = @name;
    if @year IS NOT NULL AND NOT @name IS NULL
        SELECT *
          FROM Film f, Film_Award fa
         WHERE f.isan = fa.id_film
           AND fa.award_year = @year
           AND fa.award_name = @name;
    

    RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata by time interval
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_film_between_dates
    @d1     SMALLDATETIME,
    @d2     SMALLDATETIME
AS
BEGIN

    PRINT ' * Display films between '+CAST(@d1 AS NVARCHAR(15))+' and '+CAST(@d2 AS NVARCHAR(15))
    SELECT *
      FROM Film
     WHERE release_date BETWEEN @d1 AND @d2;
END
GO

-------------------------------------------------------------------------------
-- Procedure to display a film and related metadata by time interval
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_display_edition_by_film
    @isan     BIGINT
AS
BEGIN
    IF @isan <= 0
        BEGIN
            PRINT '[P_DISPLAY_EDITION_BY_FILM] ISAN shall be positive.'
            RETURN -1
        END

    PRINT ' * Display editions for film '+CAST(@isan AS NVARCHAR(15))+'... '
    SELECT *
      FROM Edition
     WHERE id_film = @isan;
END
GO

-------------------------------------------------------------------------------
-- Procedure to insert a person
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_person
    @firstname  NVARCHAR(64),
    @lastname   NVARCHAR(64),
    @birthdate  SMALLDATETIME,
    @country    NVARCHAR(64)
AS


IF EXISTS (SELECT * 
        FROM Person 
        WHERE firstname=@firstname
        AND lastname=@lastname 
        AND birthdate=@birthdate)
    BEGIN
        PRINT '[P_INSERT_PERSON] Error: Person already exists in db: '+@firstname+' '+@lastname+'.'
        RETURN -1
    END
ELSE
    BEGIN
        IF @firstname = NULL OR @firstname = '' OR @lastname = NULL OR @lastname = '' OR @birthdate = NULL OR @country = NULL OR @country = ''
            BEGIN
                PRINT '[P_INSERT_PERSON] All parameters shall be filled.'
                RETURN -1
            END
        PRINT '[P_INSERT_PERSON] Inserting a person in db: '+@firstname+' '+@lastname+'.'
        INSERT INTO Person VALUES(@firstname, @lastname, @birthdate, @country)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to insert genres
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_genre
    @name       NVARCHAR(64)
AS
IF EXISTS (SELECT *
             FROM Genre
            WHERE name=@name)
    BEGIN
        PRINT '[P_INSERT_GENRE] Error: Genre already exists in db: '+@name+'.'
        RETURN -1
    END
ELSE
    BEGIN
        IF @name = NULL OR @name = ''
            BEGIN
                PRINT '[P_INSERT_GENRE] All parameters shall be filled.'
                RETURN -1
            END

        PRINT '[P_INSERT_GENRE] Inserting a genre in db: '+@name+'.'
        INSERT INTO Genre VALUES(@name)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to insert awards
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_award
    @name   NVARCHAR(64),
    @year   NVARCHAR(64)
AS
IF EXISTS (SELECT *
             FROM Award
            WHERE name=@name
              AND year=@year)
    BEGIN
        PRINT '[P_INSERT_AWARD] Error: Award already exists in db: '+@name+' for the year: '+@year+'.'
        RETURN -1
    END
ELSE
    BEGIN
        IF @name = NULL OR @name = '' OR @year <= 1
            BEGIN
                PRINT '[P_INSERT_AWARD] All parameters shall be filled.'
                RETURN -1
            END
        PRINT '[P_INSERT_AWARD] Inserting an award in db: '+@name+' for the year: '+@year+'.'
        INSERT INTO Award VALUES(@name, @year)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to insert a film
--  film is set so there is no copies associated
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_film
    @isan           BIGINT,
    @title          NVARCHAR(64),
    @complement     NVARCHAR(64),
    @release_date   SMALLDATETIME
AS
IF EXISTS (SELECT *
             FROM Film
            WHERE isan = @isan)
    BEGIN
        IF @isan <= 0 OR @title = NULL OR @title = '' OR @complement = NULL OR @complement = '' OR @release_date = NULL
            BEGIN
                PRINT '[P_INSERT_FILM] All parameters shall be filled.'
                RETURN -1
            END

        PRINT '[P_INSERT_FILM] Error: Film already exists in db: '+CAST(@isan AS NVARCHAR(128))+'.'
        RETURN -1
    END
ELSE
    BEGIN
        PRINT '[P_INSERT_FILM] Inserting a movie in database : '+CAST(@isan AS NVARCHAR(128))+' with success.'
        INSERT INTO Film VALUES(@isan, @title, @complement, @release_date, 1)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to associate a film with a person
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_link_played_in
    @film           BIGINT,
    @firstname      NVARCHAR(64),
    @lastname       NVARCHAR(64),
    @role           NVARCHAR(64)
AS
IF EXISTS (SELECT *
             FROM Played_In
            WHERE firstname = @firstname
              AND lastname = @lastname
              AND role = @role
              AND id_film = @film)
    BEGIN
        PRINT '[P_LINK_PLAYED_IN] Error: Link between movie: '+CAST(@film AS NVARCHAR(128))+' AND '+@firstname+' '+@lastname+' for role '+@role+' already exists.'
        RETURN -1
    END
ELSE
    BEGIN
        PRINT '[P_LINK_PLAYED_IN] Linking film: '+CAST(@film AS NVARCHAR(128))+' AND '+@firstname+' '+@lastname+'.'
        INSERT INTO Played_In VALUES(@film, @firstname, @lastname, @role)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure to associate a film with a genre
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_link_film_genre
    @film           BIGINT,
    @genre          NVARCHAR(64)
AS
IF EXISTS (SELECT *
             FROM Film_Genre
            WHERE id_film = @film
              AND genre_name = @genre)
    BEGIN
        PRINT '[P_LINK_GENRE] Error: Link between movie: '+CAST(@film AS NVARCHAR(128))+' AND Genre '+@genre+' already exists.'
        RETURN -1
    END
ELSE
    BEGIN
        PRINT '[P_LINK_GENRE] Linking movie: '+CAST(@film AS NVARCHAR(128))+' AND Genre '+@genre+'.'
        INSERT INTO Film_Genre VALUES(@film, @genre)
        RETURN 0
    END
GO


-------------------------------------------------------------------------------
-- Procedure to associate a film with an award
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_link_film_award
    @film           BIGINT,
    @award_name     NVARCHAR(256),
    @award_year     INTEGER
AS
IF EXISTS (SELECT *
             FROM Film_Award
            WHERE id_film = @film
              AND award_name = @award_name
              AND award_year = @award_year)
    BEGIN
        PRINT '[P_LINK_AWARD] Error: Link between movie: '+CAST(@film AS NVARCHAR(128))+' AND award : "'+@award_name+'" of year "'+@award_year+'".'
        RETURN -1
    END
ELSE
    BEGIN
        PRINT '[P_LINK_AWARD] Linking movie: '+CAST(@film AS NVARCHAR(128))+' AND award : "'+@award_name+'" of year "'+CAST(@award_year AS NVARCHAR(64))+'".'
        INSERT INTO Film_Award VALUES(@film, @award_name, @award_year)
        RETURN 0
    END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new persons from a charindex list
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_new_persons
    @film     AS BIGINT,
    @string   AS NVARCHAR(4000)
AS BEGIN
    DECLARE @b INTEGER, @e INTEGER
    DECLARE @ret INTEGER

    SET @b = 1
  
    IF @film IS NULL
        BEGIN
            PRINT '[P_INSERT_NEW_AWARDS] Error: missing film'
            RETURN -1
        END
    IF @string IS NULL
        BEGIN
            PRINT '[P_INSERT_NEW_AWARDS] Error: missing genre charindex string'
            RETURN -1
        END
    PRINT '[P_INSERT_NEW_PERSONS] Adding a charindex array of Persons'

    --
    WHILE @b <> LEN(@string)
        BEGIN
            DECLARE @comma1 INTEGER, @comma2 INTEGER 
            DECLARE @comma3 INTEGER, @comma4 INTEGER
            DECLARE @firstname NVARCHAR(64), @lastname NVARCHAR(64)
            DECLARE @birthdate SMALLDATETIME, @country NVARCHAR(64)
            DECLARE @role NVARCHAR(64)

            SET @comma1 = CHARINDEX(',', @string, @b+1)
            SET @comma2 = CHARINDEX(',', @string, @comma1+1)
            SET @comma3 = CHARINDEX(',', @string, @comma2+1)
            SET @comma4 = CHARINDEX(',', @string, @comma3+1)

            SET @e = CHARINDEX('|', @string, @b+1)

            SET @firstname = LTRIM(SUBSTRING(@string, @b + 1, @comma1 - @b - 1))
            SET @lastname = LTRIM(SUBSTRING(@string, @comma1 + 1, @comma2 - @comma1 - 1))
            SET @birthdate = LTRIM(SUBSTRING(@string, @comma2 + 1, @comma3 - @comma2 - 1))
            SET @country = LTRIM(SUBSTRING(@string, @comma3 + 1, @comma4 - @comma3 - 1))
            SET @role = LTRIM(SUBSTRING(@string, @comma4 + 1, @e - @comma4 - 1))

            SET @b = @e

            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM Person 
                                WHERE firstname = @firstname
                                  AND lastname = @lastname
                                  AND birthdate = @birthdate)
                    BEGIN
                        -- insert values in tables
                        EXECUTE @ret = p_insert_person @firstname, @lastname, @birthdate, @country
                        IF (@ret < 0)
                            RETURN -1
                        EXECUTE @ret = p_link_played_in @film, @firstname, @lastname, @role 
                        IF (@ret < 0)
                            RETURN -1
                    END
                ELSE
                    BEGIN
                        EXECUTE @ret = p_link_played_in @film, @firstname, @lastname, @role 
                        IF (@ret < 0)
                            RETURN -1
                    END
            END
        END
        RETURN 0
END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new genres from a list of genres
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_new_genres
    @film     AS BIGINT,
    @string   AS NVARCHAR(4000)
AS BEGIN
    DECLARE @b INTEGER, @e INTEGER
    DECLARE @ret INTEGER

    SET @b = 1

    IF @film IS NULL
        BEGIN
            PRINT '[P_INSERT_NEW_GENRES] Error: missing film'
            RETURN -1
        END
    PRINT '[P_INSERT_NEW_GENRES] Adding a charindex array of Genres'
    --
    WHILE @b <> LEN(@string)
        BEGIN
            DECLARE @name NVARCHAR(42)

            SET @e = CHARINDEX('|', @string, @b+1)

            SET @name = LTRIM(SUBSTRING(@string, @b + 1, @e - @b - 1))

            SET @b = @e

            BEGIN
                IF NOT @name IS NULL
                    IF NOT EXISTS (SELECT * 
                                     FROM Genre 
                                    WHERE name = @name)
                        BEGIN
                            -- insert values in tables
                            EXECUTE @ret = p_insert_genre @name
                            IF (@ret < 0)
                                RETURN -1
                            EXECUTE @ret = p_link_film_genre @film, @name
                            IF (@ret < 0)
                                RETURN -1
                        END
                    ELSE
                        BEGIN
                            EXECUTE @ret = p_link_film_genre @film, @name
                            IF (@ret < 0)
                                RETURN -1
                        END
            END
        END
END
GO

-------------------------------------------------------------------------------
-- Procedure for adding new awards from a charindex list
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_insert_new_awards
    @film     AS BIGINT,
    @string   AS NVARCHAR(4000)
AS BEGIN
    DECLARE @b INTEGER, @e INTEGER
    DECLARE @ret INTEGER

    SET @b = 1

    IF @film IS NULL
        BEGIN
            PRINT '[P_INSERT_NEW_AWARDS] Error: missing film'
            RETURN -1
        END
    IF LEN(@string) = 0
        RETURN 0

    PRINT '[P_INSERT_NEW_AWARDS] Adding a charindex array of Awards'
    --
    WHILE @b <> LEN(@string)
        BEGIN
            DECLARE @comma1 INTEGER
            DECLARE @name NVARCHAR(256)
            DECLARE @year INTEGER

            SET @comma1 = CHARINDEX(',', @string, @b+1)

            SET @e = CHARINDEX('|', @string, @b+1)

            SET @name = LTRIM(SUBSTRING(@string, @b + 1, @comma1 - @b - 1))
            SET @year = LTRIM(SUBSTRING(@string, @comma1 + 1, @e - @comma1 - 1))

            SET @b = @e

            BEGIN
                IF NOT @name IS NULL AND NOT @year IS NULL
                    IF NOT EXISTS (SELECT * 
                                     FROM Award 
                                    WHERE name = @name
                                      AND year = @year)
                        BEGIN
                            -- insert values in tables
                            EXECUTE @ret = p_insert_award @name, @year
                            IF (@ret < 0)
                                RETURN -1
                            EXECUTE @ret = p_link_film_award @film, @name, @year
                            IF (@ret < 0)
                                RETURN -1
                        END
                    ELSE
                        BEGIN
                            EXECUTE @ret = p_link_film_award @film, @name, @year
                            IF (@ret < 0)
                                RETURN -1
                        END
            END
        END
END
GO

-------------------------------------------------------------------------------
-- Procedure for adding a new movie
-- By: Bernard Pratz, Jonathan Dorival
-------------------------------------------------------------------------------
CREATE PROCEDURE p_new_film
    @isan           AS BIGINT,
    @title          AS NVARCHAR(64),
    @complement     AS NVARCHAR(64),
    @release_date   AS SMALLDATETIME,
    @awardString    AS NVARCHAR(4000),
    @personString   AS NVARCHAR(4000),
    @genreString    AS NVARCHAR(4000)
AS
BEGIN
    DECLARE @ret INTEGER
    
    BEGIN TRANSACTION T_NEW_FILM

    PRINT '[NEW_FILM] Adding film '''+@title+'''...'

    EXECUTE @ret = p_insert_film @isan, @title, @complement, @release_date
    IF (@ret < 0)
        GOTO P_NEW_FILM__FAILURE

    EXECUTE @ret = p_insert_new_persons @isan, @personString
    IF (@ret < 0)
        GOTO P_NEW_FILM__FAILURE

    EXECUTE @ret = p_insert_new_genres @isan, @genreString
    IF (@ret < 0)
        GOTO P_NEW_FILM__FAILURE

    EXECUTE @ret = p_insert_new_awards @isan, @awardString
    IF (@ret < 0)
        GOTO P_NEW_FILM__FAILURE

    PRINT '[NEW_FILM] Success: changes were comitted'
    COMMIT TRANSACTION T_NEW_FILM
    RETURN 0

P_NEW_FILM__FAILURE:
    PRINT '[NEW_FILM] Failure: End of Transaction, changes aren''t comitted.'
    ROLLBACK TRANSACTION T_NEW_FILM
    RETURN -1

END
GO

